package com.owen.he.jee.web.excel;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import com.owen.he.jee.utils.excel.ExcelColumnPojo;
import com.owen.he.jee.utils.excel.MCell;
import com.owen.he.jee.utils.excel.MColumn;
import com.owen.he.jee.utils.excel.MField;
import com.owen.he.jee.utils.excel.MSheet;
import com.owen.he.jee.utils.excel.MWorkBook;
import com.owen.he.jee.utils.excel.TableHead;

/**
 * 
 * @author NongFei
 *
 */
public class ExcelViewExporter {

    private static final Logger logger = Logger.getLogger(ExcelViewExporter.class);
    private static HSSFWorkbook workbook;
    public static final int MAX_ROW_PER_SHEET = 65536;

    private String fileName;

    private List<Map> excelData;
    
    /**
     * 导出的Excel模型
     */
    private MWorkBook mworkbook;

    public ExcelViewExporter(){}
    
    public ExcelViewExporter(MWorkBook wb){
    	this.mworkbook = wb;
    }

    public void setWorkbook(HSSFWorkbook workbook){
        this.workbook = workbook;
    }

    public String getFileName(){
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public void setExcelData(List<Map> excelData) {
        this.excelData = excelData;
    }


	public void generateData() {
		if (this.mworkbook != null) {
			MSheet msheet = mworkbook.getSheets().get(0);
			
            HSSFSheet sheet = workbook.createSheet(msheet.getTitle());
            
            setSheetTableHeadStyle(sheet,msheet);
            drawSheetTableHead(sheet,msheet);
            drawSheetBody(sheet,msheet);
            setSheetColumnStyle(sheet,msheet);
		}
	}

	private void drawSheetTableHead(HSSFSheet sheet,MSheet msheet){
		TableHead head = msheet.getTableHead();
		int thHeight = head.getHeight();
		for(int i=0;i<thHeight;i++){
			sheet.createRow(i);
		}
		List<MCell> cells = head.getCells();
		for(MCell mcell :cells){
			int rowIndex = mcell.getRowIndex();
			int colIndex = mcell.getColIndex();
			HSSFRow sheetRow = sheet.getRow(rowIndex);
			HSSFCell cell = sheetRow.createCell(colIndex);
			cell.setCellValue(mcell.getValue());
			
		}
	}
	

	private void setSheetTableHeadStyle(HSSFSheet sheet,MSheet msheet){
		TableHead head = msheet.getTableHead();
		int thHeight = head.getHeight();
		for(int i=0;i<thHeight;i++){
			sheet.createRow(i);
		}
		List<MCell> cells = head.getCells();
		for(MCell mcell :cells){
			String width = mcell.getWidth();
			String height = mcell.getHeight();
			int colspan = mcell.getColspan();
			int rowspan = mcell.getRowspan();
			
			int colIndex = mcell.getColIndex();
			int rowIndex = mcell.getRowIndex();
//			HSSFRow sheetRow = sheet.getRow(rowIndex);

			// 四个参数分别是：起始行，起始列，结束行，结束列    
			if(colspan>0)
				//sheet.addMergedRegion(new Region(rowIndex,(short)colIndex, rowIndex, (short)(colIndex+colspan-1))); 
            	sheet.addMergedRegion(new CellRangeAddress(rowIndex,(short)colIndex, rowIndex, (short)(colIndex+colspan-1)));
			if(rowspan>0)
            	sheet.addMergedRegion(new CellRangeAddress(rowIndex,(short)colIndex, rowIndex+rowspan-1, (short)colIndex));
				//sheet.addMergedRegion(new Region(rowIndex,(short)colIndex, rowIndex+rowspan-1, (short)colIndex)); 
		}
	}
	

	private void setSheetColumnStyle(HSSFSheet sheet,MSheet msheet){
		List<MColumn> cols = msheet.getColumns();
		for(int colIndex=0;colIndex<cols.size();colIndex++){
			MColumn mcol = cols.get(colIndex);
			String width = mcol.getWidth();
			String align = mcol.getAlign();
			String frozen = mcol.getFrozen();
			String format = mcol.getFormat();
			
			HSSFCellStyle style = workbook.createCellStyle(); // 样式对象    
			style.setAlignment(getAlignment(align));
			sheet.setDefaultColumnStyle(colIndex, style);
		}
	}
	
	private void drawSheetBody(HSSFSheet sheet,MSheet msheet){
		List<MField> fields = msheet.getFields();
		int rowIndex = msheet.getTableHead().getHeight();
		
		for(Map rowData:this.excelData){
			HSSFRow row = sheet.createRow(rowIndex);
			for(int colIndex = 0;colIndex<fields.size();colIndex++){
				MField mfield = fields.get(colIndex);
				String field = mfield.getName();
				String type = mfield.getType();
				String format = mfield.getFormat();
				
				Object value = rowData.get(field);
				HSSFCell cell = row.createCell(colIndex);
				setCellValue(cell, value);
			}
			++rowIndex;
		}
	}
	
	    
    /**
     * 设置边框
     * @param style
     */
	private void setBorder(HSSFCellStyle style) {
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	}
    /**
     * 设置必填背景颜色
     * @param style
     * @param req
     */
	private void setReqBackground(HSSFCellStyle style, String req) {
		if(!"".equals(req)&&"Y".equals(req)){
			style.setFillForegroundColor(IndexedColors.RED.getIndex());
		}else{
			style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		}
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	}
    /**
     *  填充数据
     * @param sheet
     * @param alignMap
     * @param extendRow2
     */
//    private void exportExcelData(HSSFSheet sheet,HSSFDataFormat format) {
//    	int rowIndex = extendRow;
//    	HSSFCellStyle style=null;
//    	style = workbook.createCellStyle(); // 样式对象    
//    	style.setDataFormat(format.getFormat("@"));  //设置为文本格式；
//        HSSFFont font=workbook.createFont();
//        font.setFontHeightInPoints((short)12);
//        //把字体应用到当前的样式
//        style.setFont(font);
//        setBorder(style);
//    	for(int i = 0; i < excelData.size(); i++){
//    		HSSFRow row = sheet.createRow((int) rowIndex+i);
//			Map map = (Map) excelData.get(i);
//			for (int j = 0; j < headerColList.size(); j++) {
//				ExcelColumnPojo columns=headerColList.get(j);
//				style.setAlignment(getAlignment(columns.getAlign()));
//				String text="";
//				if(map.containsKey(columns.getName().toUpperCase())){
//					text=map.get(columns.getName().toUpperCase()).toString();
//				}else{
//					int col=0;
//					for(Iterator iter = map.entrySet().iterator(); iter.hasNext(); ){
//						Map.Entry en = (Entry) iter.next();
//						if(col == columns.getIndex()){
//							text=(String) en.getValue();
//							break;
//						}
//						col++;
//					}
//				}
//				HSSFCell cell = row.createCell(columns.getIndex());   
//				cell.setCellValue(text);
//				cell.setCellStyle(style); // 样式
//			}
//			
//		}
//	}

	/**
     * 设置单元格格式；
     * @param sheet
     * @param style
     * @param cellIndex
     * @param pojo
     * @param cell
     */
	private void setCellFormat(HSSFSheet sheet, HSSFCellStyle style,
			int cellIndex, ExcelColumnPojo pojo, HSSFCell cell) {
		cell.setCellValue(pojo.getText());
		cell.setCellStyle(style); // 样式
		int intColWidth = 0;
		if (pojo.getWidth() == null || pojo.getWidth().equals(""))
			intColWidth = 80 / 6;
		else
		  intColWidth = Integer.valueOf(pojo.getWidth()).intValue();
		sheet.setColumnWidth(cellIndex, 44 * intColWidth);//列宽
		//批注
		if(StringUtils.isNotBlank(pojo.getAnnotate())){
			HSSFPatriarch p=sheet.createDrawingPatriarch(); 
			HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
			comment.setString(new HSSFRichTextString(pojo.getAnnotate())); 
			cell.setCellComment(comment); 
		}
		setBorder(style);//设置边框
	}
   /**
    * 对齐方式
    * @return
    */
    private short getAlignment(String align) {
    	if(StringUtils.isNotBlank(align)){
    		if(align.equalsIgnoreCase("center")){
    			return HSSFCellStyle.ALIGN_CENTER;
    		}else if(align.equalsIgnoreCase("left")){
    			return HSSFCellStyle.ALIGN_LEFT;
    		}else if(align.equalsIgnoreCase("right")){
    			return HSSFCellStyle.ALIGN_RIGHT;
    		}else if(align.equalsIgnoreCase("general")){
    			return HSSFCellStyle.ALIGN_GENERAL;
    		}else if(align.equalsIgnoreCase("fill")){
    			return HSSFCellStyle.ALIGN_FILL;
    		}else if(align.equalsIgnoreCase("justify")){
    			return HSSFCellStyle.ALIGN_JUSTIFY;
    		}
    	}
		return HSSFCellStyle.ALIGN_CENTER;
	}

	/**
     * 根据xmlDao解析出相应的值，生成对象
     */
	private void buildExcelColumnPojo() {}

	/**
     */
    public void generateCell(List<Map> data, HSSFRow row,
                             int columns){
        for(int j = 0; j < columns ; j++){
            HSSFCell cell = row.createCell(j);
            setCellValue(cell, data.get(0).get(j)) ;
        }
    }
    /**
     * Builds the excel cell with the specified cell value.
     * @param cell   cell object
     * @param value  specified value object
     */

    public void setCellValue(HSSFCell cell, Object value) {
        if (value == null) {
            cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        }else if (value instanceof String) {
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(new HSSFRichTextString((String) value));
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
//            cell.setCellStyle(getDefaultStyle());
        } else if (value instanceof BigDecimal){
            cell.setCellValue(((BigDecimal)value).doubleValue());
        } else if(value instanceof BigInteger){
            cell.setCellValue(((BigInteger)value).doubleValue());
        }  else if(value instanceof Long){
            cell.setCellValue(((Long)value).longValue());
        } else{
            logger.info("can't set CellValue with " + value.getClass() + " class type");
        }
    }

    
}